package com.sf.mysql.tool.utils;

import com.sf.mysql.tool.Parameters;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import com.sf.mysql.tool.vo.ColumnInfo;
import com.sf.mysql.tool.vo.TableVo;

import java.util.*;

/**
 * 数据库操作
 * 
 * @author hxl
 * @date 2012-6-28上午10:21:35
 */
public final class DBUtils {

    public static final String TABLE_NAME = "table_name";
    public static final String COLUMN_TYPE = "column_type";
    public static final String DATA_TYPE = "DATA_TYPE";
    public static final String COLUMN_KEY = "column_key";
    public static final String EXTRA = "extra";
    public static final String IS_NULLABLE = "is_nullable";
    public static final String COLUMN_COMMENT = "column_comment";
    private static final String TABLE_COMMENT = "table_comment";
    public static final String COLUMN_NAME = "column_name";
    public static final String COLUMN_DEFAULT = "COLUMN_DEFAULT";
    public static final String CHARACTER_MAXIMUM_LENGTH = "CHARACTER_MAXIMUM_LENGTH";
    public static final String NO = "否";
    public static final String YES = "是";
    private Parameters parameters = null;

    private Connection conn       = null;

    private Statement  stm        = null;

    private ResultSet  rs         = null;

    protected static final Map<String,String> IS_NULLABLE_MAP = new HashMap<>();

    public static final String COM_MYSQL_JDBC_DRIVER = "com.mysql.jdbc.Driver";

    static {
        try {
            // 加载数据库驱动
            Class.forName(COM_MYSQL_JDBC_DRIVER);

            IS_NULLABLE_MAP.put("NO",NO);
            IS_NULLABLE_MAP.put("YES",YES);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static String getIsNullableFromMap(String key) {
        String value = "";
        if(key != null && !"".equals(key)){
            value = IS_NULLABLE_MAP.get(key);
        }
        return value;
    }

    public DBUtils(Parameters parameters){
        this.parameters = parameters;
    }

    public Map<String, HashMap<String, LinkedHashMap<String, String>>> getDatabaseInfo() throws Exception {
        getResultSet();

        Map<String, HashMap<String, LinkedHashMap<String, String>>> info = new HashMap<>();

        while (rs.next()) {
            String table_name = rs.getString(TABLE_NAME);
            HashMap<String, LinkedHashMap<String, String>> tablesMap = info.get(table_name);
            if (tablesMap == null) {
                tablesMap = new HashMap<>();
            }
            String column_name = rs.getString(COLUMN_NAME);
            LinkedHashMap<String, String> columnInfo = tablesMap.get(column_name);
            if (columnInfo == null) {
                columnInfo = new LinkedHashMap<>();
            }
            columnInfo.put(COLUMN_TYPE, rs.getString(COLUMN_TYPE));
            String key = rs.getString(COLUMN_KEY);
            if ("PRI".equals(key)) {
                key = YES;
            }

            columnInfo.put(COLUMN_KEY, key);
            columnInfo.put(EXTRA, rs.getString(EXTRA));
            columnInfo.put(IS_NULLABLE, rs.getString(IS_NULLABLE));
            columnInfo.put(COLUMN_COMMENT, rs.getString(COLUMN_COMMENT));

            tablesMap.put(column_name, columnInfo);
            info.put(table_name, tablesMap);

        }

        releaseDBResource();
        return info;
    }

    private void getResultSet() throws SQLException {
        // table_schema='数据库名'
        String sql = "select table_name , column_name ,  column_type , column_key , extra , is_nullable ,column_comment from columns where table_schema='"
                     + parameters.getDatabase() + "'";

        if (!parameters.getTable().equals("_NULL_")) {
            sql += " and table_name = '" + parameters.getTable() + "'";
        }

        conn = DriverManager.getConnection("jdbc:mysql://" + parameters.getHost() + ":" + parameters.getPort()
                                           + "/INFORMATION_SCHEMA", parameters.getUser(), parameters.getPassword());
        stm = conn.createStatement();
        rs = stm.executeQuery(sql);
    }

    private void getResultSetExt() throws SQLException {
        // table_schema='数据库名'
        String sql = "select c.table_name , column_name ,column_type,  DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, column_key , extra , is_nullable ,column_comment ,t.TABLE_COMMENT ,c.COLUMN_DEFAULT " +
                "from columns c,TABLES t " +
                "where  t.TABLE_NAME = c.TABLE_NAME and t.TABLE_SCHEMA = c.TABLE_SCHEMA and c.table_schema='"
                + parameters.getDatabase() + "'";

        if (!parameters.getTable().equals("_NULL_")) {
            sql += " and table_name = '" + parameters.getTable() + "'";
        }

        conn = DriverManager.getConnection("jdbc:mysql://" + parameters.getHost() + ":" + parameters.getPort()
                + "/INFORMATION_SCHEMA", parameters.getUser(), parameters.getPassword());
        stm = conn.createStatement();
        rs = stm.executeQuery(sql);
    }

    public boolean connectable () throws SQLException {
        Connection connection = null;//获取连接对象
        try{
            connection = DriverManager.getConnection("jdbc:mysql://" + parameters.getHost() + ":" + parameters.getPort()
                    + "/" + parameters.getDatabase(), parameters.getUser(), parameters.getPassword());
            return connection != null ;
        }catch(SQLException e){
           throw e;
        } finally {
            if(connection != null){
                connection.close();
            }
        }
    }

    public List<TableVo> getTableList () throws Exception {
        Map<String,TableVo> tablesMap = new HashMap<>();
        getResultSetExt();
        while (rs.next()) {
            String table_name = rs.getString(TABLE_NAME);
            TableVo table = tablesMap.get(table_name);
            if (table == null) {
                table = new TableVo();
                table.setTableName(table_name);
                table.setTableComment(rs.getString(TABLE_COMMENT));
                table.setColumnList(new ArrayList<ColumnInfo>());
            }
            List<ColumnInfo> columnInfoList = table.getColumnList();
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setColumnName(rs.getString(COLUMN_NAME));
            columnInfo.setDataType(rs.getString(DATA_TYPE));
            columnInfo.setNullAble(getIsNullableFromMap(rs.getString(IS_NULLABLE)));
            columnInfo.setComment(rs.getString(COLUMN_COMMENT));
            columnInfo.setColumnLength(extractNum(rs.getString(COLUMN_TYPE)));
            columnInfo.setDefaultValue(rs.getString(COLUMN_DEFAULT));
            columnInfo.setIsPrimary(NO);
            String key = rs.getString(COLUMN_KEY);
            if ("PRI".equals(key)) {
                columnInfo.setIsPrimary(YES);
            }
            columnInfoList.add(columnInfo);

            tablesMap.put(table_name, table);
        }
        releaseDBResource();
        return new ArrayList<>(tablesMap.values());
    }

    public static Integer extractNum(String str) {
        StringBuilder strBuilder = new StringBuilder();
        int result = 0;
        if (str != null && !"".equals(str)) {
            for (int i = 0; i < str.length(); i++) {
                if (str.charAt(i) >= 48 && str.charAt(i) <= 57) {
                    strBuilder.append(str.charAt(i));
                }
            }
        }
        if(strBuilder.length() >0){
            result = Integer.valueOf(strBuilder.toString());
        }
        return result;
    }

    /**
     * 释放资源
     * 
     * @throws SQLException
     */
    public void releaseDBResource() throws SQLException {
        if (null != rs) {
            rs.close();
        }

        if (null != stm) {
            stm.close();
        }

        if (null != conn) {
            conn.close();
        }
    }
}
